﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using ChinaDynastyTours.Models;
using ChinaDynastyTours.Helpers;

namespace ChinaDynastyTours.Controls
{
    public class ShopCtrl
    {

        #region 获取商店表数据
        /// <summary>
        ///  获取商店表数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GetShopNameAndIDInfo()
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = "SELECT ShopID,ShopName FROM bas_Shop";
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        public static  ShopEntity GetShopPriceAndCommissionByID(string shopId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT Price,Commission FROM bas_Shop Where ShopID={0}", shopId);
            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return new ShopEntity()
                {
                    Price = decimal.Parse(ds.Tables[0].Rows[0]["Price"].ToString()),
                    Commission = float.Parse(ds.Tables[0].Rows[0]["Commission"].ToString())
                };
            else
                return null;
        }
        public static DataTable GetShopData(string providerNamePY)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT  *  FROM bas_Shop
                               WHERE lower(dbo.f_getpinyin(ShortCut)) LIKE '{0}%' OR lower(dbo.f_getpy(ShortCut)) LIKE '{0}%'", SqlHelper.SQLCheck(providerNamePY)));

            DataTable dt = new DataTable();
            dt.Load(db.ExecuteReader(dbCommand));
            return dt;
        }
        #endregion

        #region 增、删、改商店信息
        internal static string CreateShopData(ShopEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"INSERT INTO bas_Shop([ShopName],[ShortCut], [Contact], [Phone], [Fax], [Price],[Commission],[Comment],[CityName],[ShopType] )
                                         VALUES(@ShopName, @ShortCut, @Contact, @Phone, @Fax, @Price, @Commission, @Comment,@CityName,@ShopType);Select SCOPE_IDENTITY();");
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "ShopName", DbType.String, entity.ShopName);
            db.AddInParameter(dbCommand, "ShortCut", DbType.String, entity.ShortCut);
            db.AddInParameter(dbCommand, "Contact", DbType.String, entity.Contact);
            db.AddInParameter(dbCommand, "Fax", DbType.String, entity.Fax);
            db.AddInParameter(dbCommand, "Phone", DbType.String, entity.Phone);
            db.AddInParameter(dbCommand, "Price", DbType.Decimal, entity.Price);
            db.AddInParameter(dbCommand, "Commission", DbType.Double, entity.Commission);
            db.AddInParameter(dbCommand, "Comment", DbType.String, entity.Comment);
            db.AddInParameter(dbCommand, "CityName", DbType.String, entity.CityName);
            db.AddInParameter(dbCommand, "ShopType", DbType.String, entity.ShopType);

            return db.ExecuteScalar(dbCommand).ToString();
        }

        internal static int UpdateShopData(ShopEntity entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = @"UPDATE bas_Shop SET ShopName = @ShopName, ShortCut=@ShortCut, Contact=@Contact, Phone=@Phone, Fax=@Fax,
                                                   Price=@Price, Commission=@Commission, Comment=@Comment,CityName=@CityName,ShopType=@ShopType WHERE ShopID=@ShopID";

            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            db.AddInParameter(dbCommand, "ShopName", DbType.String, entity.ShopName);
            db.AddInParameter(dbCommand, "ShortCut", DbType.String, entity.ShortCut);
            db.AddInParameter(dbCommand, "Contact", DbType.String, entity.Contact);
            db.AddInParameter(dbCommand, "Fax", DbType.String, entity.Fax);
            db.AddInParameter(dbCommand, "Phone", DbType.String, entity.Phone);
            db.AddInParameter(dbCommand, "Price", DbType.Decimal, entity.Price);
            db.AddInParameter(dbCommand, "Commission", DbType.Double, entity.Commission);
            db.AddInParameter(dbCommand, "Comment", DbType.String, entity.Comment);
            db.AddInParameter(dbCommand, "ShopID", DbType.Int32, entity.ShopID);
            db.AddInParameter(dbCommand, "CityName", DbType.String, entity.CityName);
            db.AddInParameter(dbCommand, "ShopType", DbType.String, entity.ShopType);

            return db.ExecuteNonQuery(dbCommand);
        }
        internal static int DeleteShopData(string shopId)
        {
            if (shopId == "") return 0;

            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format(@"DELETE FROM bas_Shop WHERE ShopID={0}", shopId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        #endregion
    }
}
